package gpl.pierrick.brihaye.aramorph;

import gpl.pierrick.brihaye.aramorph.lucene.ArabicNormalizer;
import gpl.pierrick.brihaye.util.Pair;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Properties;
import java.util.logging.Logger;

import javax.swing.JOptionPane;

/**
 * This class is used to handle all DB-related stuff. It applies the singleton
 * pattern; this means it has only one object created.
 * 
 * @author Ahmed Saad
 */
public class DBManager {
	/** A logger to write log messages */
	private static Logger logger = Logger.getLogger(DBManager.class.getName());

	/** The connection used to connect to data base */
	private Connection connection;

	/** The statement used to execute sql queries */
	private Statement statement;

	/** Driver name */
	private static String JDBC_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

	/** Database location for updating */
	private static String DATABASE_UPDATE_URL = "jdbc:derby:directory:dictionaries/AraMorph;create=true";

	/** Database location for readonly */
	private static String DATABASE_READONLY_URL = "jdbc:derby:classpath:/dictionaries/AraMorph";

	/** One instance that can only read from database */
	public static final DBManager readonlyConnection = new DBManager(DATABASE_READONLY_URL);

	/** One instance that can perform update operations on database */
	private static DBManager updateConnection;

	// Some statistical information
	/** No of calls to contains() */
	private static int nContains;

	/** No of calls to getEntries() */
	private static int nGetEntries;

	/**
	 * private constructor to apply the singleton pattern
	 */
	private DBManager(String databaseURL) {
		try {
			// initialize temp directory to writable loation (for read only
			// access)
			Properties p = System.getProperties();
			p.put("derby.stream.error.file", p.getProperty("java.io.tmpdir")
					+ p.getProperty("file.separator") + "araMorph.tmp");
			p.put("derby.storage.tempDirectory", p
					.getProperty("java.io.tmpdir"));

			// check that embedded driver classes exist
			Class.forName(JDBC_DRIVER);
			// connect to database
			connection = DriverManager.getConnection(databaseURL);
			// create a statement
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		} catch (SQLException e1) {
			logger.warning(e1.getMessage());
		} catch (ClassNotFoundException e) {
			logger.severe("JDBC Driver not found");
		}
	}

	/**
	 * Creates the db and tables in it in order to use
	 */
	public static void createDB() {
		DBManager db = getUpdateConnection();
		// 1- the dictionaries database
		String[] dictNames = { "PREFIXES", "STEMS", "SUFFIXES" };
		String[] cleanUpStmts = { " DROP INDEX INDEX_???",
				"DROP INDEX INDEX_???2", "DROP TABLE DICT_???" };
		// clean up
		for (String stmt : cleanUpStmts) {
			for (String dictName : dictNames) {
				try {
					String sql = stmt.replaceAll("\\Q???\\E", dictName);
					db.statement.executeUpdate(sql);
				} catch (SQLException e) {
					logger.warning(e.getMessage());
				}
			}
		}

		// create db statements
		try {
			String createStmt = "create table DICT_??? (" + "entry varchar(?),"
					+ "normalized_entry varchar(?)," + "lemma_id varchar(?),"
					+ "vocalization varchar(?)," + "morphology varchar(?),"
					+ "glosses varchar(?)," + "POS varchar(?),"
					+ "root varchar(?))";
			int[][] dictionarySizes = { { 4, 4, 1, 6, 17, 37, 27, 1 },
					{ 16, 16, 32, 20, 19, 86, 67, 7 },
					{ 6, 6, 1, 11, 13, 33, 50, 1 }, };
			for (int dictNo = 0; dictNo < dictNames.length; dictNo++) {
				// 1- create the table
				String sql = createStmt.replaceAll("\\Q???\\E",
						dictNames[dictNo]);
				for (int attNo = 0; attNo < dictionarySizes[dictNo].length; attNo++)
					sql = sql.replaceFirst("\\?", ""
							+ dictionarySizes[dictNo][attNo]);
				// statement is ready, execute it
				db.statement.executeUpdate(sql);
			}
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}

		// 2- the compatibility tables database
		String[] tableNames = { "AB", "BC", "AC" };
		String[] stmts = { " DROP TABLE TABLE_???" };
		// clean up
		for (String stmt : stmts) {
			try {
				for (String tableName : tableNames) {
					String sql = stmt.replaceAll("\\Q???\\E", tableName);
					db.statement.executeUpdate(sql);
				}
			} catch (SQLException e) {
				logger.warning(e.getMessage());
			}
		}
		// create tables
		try {
			String createStmt = "create table TABLE_??? (entry varchar(?) primary key not null)";
			int[] tableSizes = { 37, 32, 29 };
			for (int tableNo = 0; tableNo < tableNames.length; tableNo++) {
				String sql = createStmt.replaceAll("\\Q???\\E",
						tableNames[tableNo]);
				sql = sql.replaceAll("\\?", "" + tableSizes[tableNo]);
				db.statement.executeUpdate(sql);
			}
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}
	}

	public static void createIndices() {
		DBManager db = DBManager.getUpdateConnection();
		String[] dictNames = { "PREFIXES", "STEMS", "SUFFIXES" };
		String stmts[] = { "CREATE INDEX INDEX_??? ON DICT_???(entry)",
				"CREATE INDEX INDEX_???2 ON DICT_???(normalized_entry)",
				"CREATE INDEX INDEX_???3 ON DICT_???(glosses)" };
		// 2- create the indices
		for (int dictNo = 0; dictNo < dictNames.length; dictNo++) {
			for (int stmtNo = 0; stmtNo < stmts.length; stmtNo++) {
				String sql;
				sql = stmts[stmtNo].replaceAll("\\Q???\\E", dictNames[dictNo]);
				try {
					db.statement.executeUpdate(sql);
				} catch (SQLException e) {
					logger.warning(e.getMessage());
				}
			}
		}
	}

	/**
	 * clean any memory used and closes connection.
	 */
	@Override
	protected void finalize() throws Throwable {
		statement.close();
		connection.close();
		DriverManager.getConnection("jdbc:derby:;shutdown=true");
	}

	public static void writeStats() {
		logger.info("Statistics: contains:" + nContains + ", getEntries:" + nGetEntries);
	}

	/**
	 * Returns a connection that is able to update the database.
	 * 
	 * @return a DBManager that can update database (i.e. addEntry is working)
	 */
	public static DBManager getUpdateConnection() {
		if (updateConnection == null) {
			updateConnection = new DBManager(DATABASE_UPDATE_URL);
		}
		return updateConnection;
	}

	// ---------- METHODS FOR DICTIONARIES
	/**
	 * Adds the given entry to the given table
	 * 
	 * @param tableName
	 *            the table where the entry will be inserted
	 * @param de
	 *            the dictionary entry to be inserted
	 */
	public void addDictionaryEntry(String tableName, DictionaryEntry de) {
		try {
			// create sql statement
			PreparedStatement ps = connection.prepareStatement("insert into "
					+ tableName + " values(?,?,?,?,?,?,?,?)");
			ps.setString(1, de.getEntry(false));
			ps.setString(2, de.getEntry(true));
			ps.setString(3, de.getLemmaID());
			ps.setString(4, de.getVocalization());
			ps.setString(5, de.getMorphology());
			// add glosses as pipe separated list
			String glosses = null;
			for (String gloss : de.getGlosses())
				glosses = glosses == null ? gloss : ',' + gloss;
			ps.setString(6, glosses == null ? "" : glosses);
			// add POS as slash separated list
			String POS = null;
			for (String pos : de.getPOS())
				POS = POS == null ? pos : ',' + pos;
			ps.setString(7, POS == null ? "" : POS);
			// add root
			ps.setString(8, de.getRoot());

			// execute sql statement
			ps.execute();
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}
	}

	/**
	 * Tells whether the given table contains a row with the given entry or not.
	 * 
	 * @param tableName
	 *            the table to be searched
	 * @param entry
	 *            the entry which we are searching for
	 * @return <code>true</code> if the given table contains at least one row
	 *         with the given entry <code>false</code> otherwise
	 */
	public boolean containsEntry(String tableName, String entry,
			boolean normalized) {
		String colName;
		++nContains;
		
		if (normalized) {
			entry = ArabicNormalizer.normalize(entry);
			if (entry == null)
				entry = "";
			colName = "normalized_entry";
		} else
			colName = "entry";

		try {
			// create sql statement
			PreparedStatement ps = connection
					.prepareStatement("select count(*) from " + tableName
							+ " where " + colName + "=?");
			ps.setString(1, entry);

			// execute query
			ResultSet resultSet = ps.executeQuery();

			// return true if count > 0
			return resultSet.next() && resultSet.getInt(1) > 0;
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}
		return false;
	}

	/**
	 * Delete all rows from the given table
	 * 
	 * @param tableName
	 *            the table to be emptied
	 */
	public void clearTable(String tableName) {
		// create sql statement
		String sql = "delete from " + tableName;

		try {
			// execute query
			statement.execute(sql);
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}
	}

	/**
	 * Returns all dictionary entries that have the given gloss. Used for
	 * English->Arabic translation
	 * 
	 * @param gloss
	 *            the English gloss to be searched for
	 * @return all dictionary entries with the given gloss. <code>null</code>
	 *         if some error occured
	 */
	public Collection<DictionaryEntry> getDictionaryEntriesByGloss(String gloss) {
		// create sql statement
		String sql = "SELECT * FROM DICT_STEMS WHERE GLOSSES LIKE '%" + gloss
				+ "%'";

		try {
			// exectute statmenet
			ResultSet resultSet = statement.executeQuery(sql);

			// collection of results
			Collection<DictionaryEntry> entries = new ArrayList<DictionaryEntry>();
			while (resultSet.next()) {
				String entr = resultSet.getString(1);
				// column 2 is normalized entry, not retrieved
				String lemmaId = resultSet.getString(3);
				String vocalization = resultSet.getString(4);
				String morphological_category = resultSet.getString(5);
				String[] glosses = resultSet.getString(6).split("\\|");
				String[] POS = resultSet.getString(7).split("\\|");
				String root = resultSet.getString(8);

				entries.add(new DictionaryEntry(entr, lemmaId, vocalization,
						morphological_category, glosses, POS, root));
			}
			return entries;
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}
		return null;
	}

	/**
	 * Reads all entries from database and returns them in the given collection.
	 * @param tableName table name to read all its entries
	 * @param entries a collection to store all entries
	 * @return total number of rows read
	 */
	public int getAllEntris(String tableName, Collection<Pair> entries) {
		String sql = "SELECT entry FROM "+tableName;
		try {
			int nRead=0;
			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				String[] parts = resultSet.getString(1).split(" ");
				entries.add(new Pair(parts[0], parts[1]));
				++nRead;
			}
			return nRead;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * Reads all entries from database and returns them in the given collection.
	 * @param tableName table name to read all its entries
	 * @param entries a collection to store all entries
	 * @param normalized_entries a collection to store all normalized entries
	 * @return total number of rows read
	 */
	public int getAllEntries(String tableName, Collection<String> entries, Collection<String> normalized_entries) {
		String sql = "SELECT entry, normalized_entry FROM "+tableName;
		try {
			int nRead=0;
			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				entries.add(resultSet.getString(1));
				normalized_entries.add(resultSet.getString(2));
				++nRead;
			}
			return nRead;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	/**
	 * Returns all dictionary entries with the given entry string
	 * 
	 * @param tableName
	 *            the table to be searched
	 * @param entry
	 *            the entry to be searched for
	 * @param normalized
	 *            whether the given entry is normalized or not
	 * @return a collection with all dictionary entries having the given entry.
	 *         If none were found an empty collection is returned.
	 *         <code>null</code> if some error occured (e.g. database error or
	 *         wrong tableName)
	 */
	public Collection<DictionaryEntry> getDictionaryEntries(String tableName,
			String entry, boolean normalized) {
		String colName;
		nGetEntries++;
		if (normalized) {
			entry = ArabicNormalizer.normalize(entry);
			if (entry == null)
				entry = "";
			colName = "normalized_entry";
		} else
			colName = "entry";

		// create sql statement
		String sql = "select * from " + tableName + " where " + colName + "='"
				+ entry + "'";

		try {
			// exectute statmenet
			ResultSet resultSet = statement.executeQuery(sql);

			// collection of results
			Collection<DictionaryEntry> entries = new ArrayList<DictionaryEntry>();
			while (resultSet.next()) {
				String entr = resultSet.getString(1);
				// column 2 is normalized entry, not retrieved
				String lemmaId = resultSet.getString(3);
				String vocalization = resultSet.getString(4);
				String morphological_category = resultSet.getString(5);
				String[] glosses = resultSet.getString(6).split("\\|");
				String[] POS = resultSet.getString(7).split("\\|");
				String root = resultSet.getString(8);

				entries.add(new DictionaryEntry(entr, lemmaId, vocalization,
						morphological_category, glosses, POS, root));
			}
			return entries;
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}
		return null;
	}

	// ------------- METHODS FOR COMPATIBILITY TABLES
	/**
	 * Adds an entry to the given compatibility table
	 * 
	 * @param tableName
	 *            the table in which the entry will be inserted
	 * @param first
	 *            first part of the entry
	 * @param second
	 *            second part of the entry
	 */
	public void addCompatibilityEntry(String tableName, String entry) {
		// create sql statement
		try {
			PreparedStatement ps = connection.prepareStatement("insert into "
					+ tableName + " values(?)");
			ps.setString(1, entry);
			ps.execute();
		} catch (SQLException e) {
			logger.warning(e.getMessage());
		}
	}

	/**
	 * Test main
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		String entry = JOptionPane.showInputDialog(null, "enter something");
		System.err.println(DBManager.readonlyConnection.containsEntry(
				"DICT_STEMS", entry, true));
		System.exit(0);
	}
}
